using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Data.Common;

namespace FROG
{
    partial class WorkSheet
    {
        private void ExecuteCurrentCommand()
        {
            string sql = ExtractCurrentCommand();
            if (_connection != null)
            {
                dgvResults.Rows.Clear();
                trvExplain.Nodes.Clear();
                if (sql.Trim().ToLower().StartsWith("select"))
                {
                    ExecuteQuery(sql);
                }
                else
                {
                    ExecuteNonQuery(sql);
                }
            }
            else
            {
                MessageBox.Show("Not connected", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            sciSQL.Focus();
        }

        private void ExecuteNonQuery(string sql)
        {
            try
            {
                DbCommand cmd = _connection.CreateCommand();
                cmd.CommandText = sql;

                int nrows = cmd.ExecuteNonQuery();
                AppendText(string.Format("{0} rows have been affected\n\n", nrows));
                tabOutput.SelectedTab = tpOutput;
            }
            catch (DataException e)
            {
                MessageBox.Show("Error executing query:\n" + e.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                AppendError(e.Message + "\n\n");
                tabOutput.SelectedTab = tpOutput;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error :\n" + e.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                AppendError(e.Message + "\n\n");
                tabOutput.SelectedTab = tpOutput;
            }
        }

        private void ExecuteQuery(string sql)
        {
            try
            {
                DbCommand cmd = _connection.CreateCommand();
                cmd.CommandText = sql;

                IDataReader reader = cmd.ExecuteReader();
                FillGrid(reader);
                tabOutput.SelectedTab = tpResults;
            }
            catch (DataException e)
            {
                MessageBox.Show("Error executing query:\n" + e.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                AppendError(e.Message + "\n\n");
                tabOutput.SelectedTab = tpOutput;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error :\n" + e.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                AppendError(e.Message + "\n\n");
                tabOutput.SelectedTab = tpOutput;
            }
        }

        // TODO: make plan table name configurable
        private string planTableName = "FROG_PLAN_TABLE";
        private string statement_id = "frog_stmt";

        private void ExplainCurrentCommand()
        {
            if (_connection != null)
            {
                dgvResults.Rows.Clear();
                trvExplain.Nodes.Clear();
                CreatePlanTableIfNeeded();
                try
                {
                    string sql = ExtractCurrentCommand();
                    DbCommand cmd = _connection.CreateCommand();
                    cmd.CommandText =
                        string.Format("DELETE FROM {0} WHERE STATEMENT_ID = '{1}'",
                                        planTableName,
                                        statement_id);
                    cmd.ExecuteNonQuery();

                    cmd = _connection.CreateCommand();
                    cmd.CommandText =
                        string.Format("EXPLAIN PLAN SET STATEMENT_ID = '{0}' INTO {1} FOR {2}",
                                        statement_id,
                                        planTableName,
                                        sql);
                    cmd.ExecuteNonQuery();

                    cmd = _connection.CreateCommand();
                    cmd.CommandText = string.Format(Properties.Resources.selectPlan, planTableName, statement_id);

                    using (IDataReader rd = cmd.ExecuteReader())
                    {
                        FillTree(rd);
                        rd.Close();
                    }
                    tabOutput.SelectedTab = tpExplain;
                }
                catch (DbException ex)
                {
                    MessageBox.Show("Error explaining the query:\n" + ex.Message);
                    AppendError(ex.Message + "\n\n");
                    tabOutput.SelectedTab = tpOutput;
                }
            }
            else
            {
                MessageBox.Show("Not connected", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            sciSQL.Focus();
        }

        private void FillTree(IDataReader rd)
        {
            Dictionary<object, CommonTools.Node> nodes = new Dictionary<object, CommonTools.Node>();
            while (rd.Read())
            {
                object[] fields = new object[7];
                fields[0] = rd["OPERATION"];
                fields[1] = rd["OPTIONS"];
                fields[2] = rd["OBJECT_OWNER"];
                fields[3] = rd["OBJECT_NAME"];
                fields[4] = rd["COST"];
                fields[5] = rd["CARDINALITY"];
                fields[6] = rd["TEXT"];
                object id = rd["ID"];
                object parent_id = rd["PARENT_ID"];
                CommonTools.Node node = new CommonTools.Node(fields);
                node.Tag = id;
                nodes.Add(id, node);
                if (nodes.ContainsKey(parent_id))
                {
                    CommonTools.Node parentNode = nodes[parent_id];
                    parentNode.Nodes.Add(node);
                }
                else
                {
                    trvExplain.Nodes.Add(node);
                }
            }
            trvExplain.ExpandAll();
            trvExplain.Columns.RecalcVisibleColumsRect();
        }

        private void CreatePlanTableIfNeeded()
        {
            try
            {
                DbCommand cmd = _connection.CreateCommand();
                cmd.CommandText = string.Format("SELECT COUNT(1) FROM all_tables WHERE table_name = '{0}' AND owner = USER", planTableName);
                int n = (int)cmd.ExecuteScalar();
                if (n == 0)
                {
                    cmd = _connection.CreateCommand();
                    cmd.CommandText = string.Format(Properties.Resources.ddlPlanTable, planTableName);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (DbException e)
            {
                MessageBox.Show("Error creating plan table:\n" + e.Message);
                AppendError(e.Message + "\n\n");
                tabOutput.SelectedTab = tpOutput;
            }
        }

        private string ExtractCurrentCommand()
        {
            if (sciSQL.Selection.Length == 0)
            {
                int currentLine = sciSQL.Caret.LineNumber;
                int firstLine = 0, lastLine = currentLine;
                for (int line = currentLine - 1; line >= 0; line--)
                {
                    if (sciSQL.Lines[line].Text.Trim().Length == 0)
                    {
                        firstLine = line + 1;
                        break;
                    }
                }
                for (int line = currentLine; line < sciSQL.Lines.Count; line++)
                {
                    if (sciSQL.Lines[line].Text.Trim().Length == 0)
                    {
                        lastLine = line - 1;
                        break;
                    }
                    else if (line == sciSQL.Lines.Count - 1)
                    {
                        lastLine = sciSQL.Lines.Count - 1;
                    }
                }

                sciSQL.Selection.Start = sciSQL.Lines[firstLine].StartPosition;
                sciSQL.Selection.End = sciSQL.Lines[lastLine].EndPosition;
            }

            return sciSQL.Selection.Text.Trim().TrimEnd(';');
        }

        private void ExecuteScript()
        {
            if (_connection != null)
            {
                string script = null;
                if (sciSQL.Selection.Length > 0)
                    script = sciSQL.Selection.Text;
                else
                    script = sciSQL.Text;

                string[] sql_commands = script.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string sql in sql_commands)
                {
                    ExecuteNonQuery(sql);
                }
            }
            else
            {
                MessageBox.Show("Not connected", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            sciSQL.Focus();
        }

        private IDataReader _currentResult = null;
        private int _maxFetch = 50;

        private int FillGrid(IDataReader rd)
        {
            if (_currentResult != null && !_currentResult.IsClosed)
                _currentResult.Close();
            _currentResult = rd;

            // Clear grid
            dgvResults.Rows.Clear();
            dgvResults.Columns.Clear();

            // Create columns
            for (int i = 0; i < rd.FieldCount; i++)
            {
                string name = rd.GetName(i);
                dgvResults.Columns.Add(name, name);
                string typeName = rd.GetDataTypeName(i);
                if (typeName == null)
                    typeName = "(Unknown type)";
                dgvResults.Columns[i].Tag = typeName;
            }

            // Fill data
            int nRows = 0;
            bool moreRows = false;
            while (rd.Read())
            {
                object[] values = new object[rd.FieldCount];
                for (int i = 0; i < rd.FieldCount; i++)
                {
                    if (Connection.IsBlobType(dgvResults.Columns[i].Tag.ToString()))
                    {
                        try
                        {
                            long length = rd.GetBytes(i, 0, null, 0, 0);
                            values[i] = length;
                        }
                        catch (InvalidCastException)
                        {
                            values[i] = null;
                        }
                    }
                    else values[i] = rd[i];
                }
                //rd.GetValues(values);
                dgvResults.Rows.Add(values);
                nRows++;
                if (nRows >= _maxFetch)
                {
                    moreRows = true;
                    break;
                }
            }

            if (!moreRows)
            {
                rd.Close();
                _currentResult = null;
            }

            return nRows;
        }

        private void FetchNextResults()
        {
            if (_currentResult != null)
            {
                IDataReader rd = _currentResult;
                int nRows = 0;
                bool moreRows = false;
                while (rd.Read())
                {
                    object[] values = new object[rd.FieldCount];
                    for (int i = 0; i < rd.FieldCount; i++)
                    {
                        values[i] = rd.GetValue(i);
                    }
                    dgvResults.Rows.Add(values);
                    nRows++;
                    if (nRows >= _maxFetch)
                    {
                        moreRows = true;
                        break;
                    }
                }

                if (!moreRows)
                {
                    rd.Close();
                    _currentResult = null;
                }
            }
        }

    }
}
